﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Collections;

namespace Code.Search
{
    public class SearchDataAccess
    {
        protected DataProvider m_DataProvider = DataProvider.GlobalDataProvider;
        public SearchDataAccess()
        {

        }

        public DataTable DoSearch(string strCondition)
        {
            strCondition = strCondition.ToUpper();
            char[] s={' '};
            string[] sCon = strCondition.Split(s);
            ArrayList arrIDList = new ArrayList();
            //string sSqlSelectProductType = "SELECT * FROM [LOAI_SAN_PHAM]";
            string sSqlSelectProductType = "select ID,Tensanpham from san_pham";
            DataTable dt = m_DataProvider.ExecuteQueryDataTable(sSqlSelectProductType);
            if ((dt == null) || (dt.Rows.Count <= 0))
                return null;
            string sProductType = "";
            bool bIsFound = false;
            ArrayList arrTemp = new ArrayList();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sProductType = dt.Rows[i][1].ToString();
                sProductType = sProductType.ToUpper();
                bool bContain = false;
                for (int k = 0; k < sCon.Length; k++)
                {
                    if (sProductType.Contains(sCon[k]))
                    {
                        bContain = true;     
                        break;
                    }
                }
                if (bContain)
                {
                    arrTemp.Add(dt.Rows[i][0]);
                }
                //    bIsFound = true;
                //    string sSQLSelectProductName = "SELECT ID,TENSANPHAM FROM SAN_PHAM WHERE IDLOAISANPHAM = " + dt.Rows[i][0].ToString();
                //    DataTable dtProduct = m_DataProvider.ExecuteQueryDataTable(sSQLSelectProductName);
                //    if (dtProduct == null || dtProduct.Rows.Count <= 0)
                //    {
                //        break;
                //    }
                //    ArrayList arrTemp = new ArrayList();
                //    for (int j = 0; j < dtProduct.Rows.Count; j++)
                //    {
                //        arrTemp.Add(dtProduct.Rows[j][0]);
                //        string strName = dtProduct.Rows[j][1].ToString();
                //        strName = strName.ToUpper();
                //        bContain = false;
                //        for (int k = 0; k < sCon.Length; k++)
                //        {
                //            if (strName.Contains(sCon[k]))
                //            {
                //                bContain = true;
                //                break;
                //            }
                //        }
                //        if (bContain)
                //        {
                //            arrIDList.Add(dtProduct.Rows[j][0].ToString());
                //        }
                //    }
                //    if (arrIDList.Count <= 0)
                //    {
                //        for (int j = 0; j < dtProduct.Rows.Count; j++)
                //        {
                //            arrIDList.Add(dtProduct.Rows[j][0].ToString());
                //        }
                //    }
                //}
                //if (bIsFound)
                //    break;
            }

            bIsFound = true;
            //SAN_PHAM.idLoaiSanPham =
            string sSQLSelectProduct = "SELECT HINH_ANH.Url, SAN_PHAM.id,SAN_PHAM.Gia, SAN_PHAM.TenSanPham, SAN_PHAM.MoTa, SAN_PHAM.IdLoaiSanPham FROM HINH_ANH, SAN_PHAM WHERE HINH_ANH.idSanPham = SAN_PHAM.id AND ( ";

            foreach (object obj in arrTemp)
            {
                if(bIsFound)
                {
                    sSQLSelectProduct += "SAN_PHAM.id = " + obj.ToString();
                    bIsFound = false;
                }
                else
                {
                    sSQLSelectProduct += " OR ";
                    sSQLSelectProduct += "SAN_PHAM.id = " + obj.ToString();
                }

            }
            sSQLSelectProduct += ")";

            return m_DataProvider.ExecuteQueryDataTable(sSQLSelectProduct);
        }

        public DataTable DoAdvanceSearch(string sType,string sManufacture,int iBeginMoney,int iEndMoney)
        {
            string sBeginPriceCompare ="";
            if(iBeginMoney == 0)
                sBeginPriceCompare = "";
            else
                sBeginPriceCompare = " AND SAN_PHAM.GIA >= " +iBeginMoney.ToString();

             string sEndPriceCompare ="";
            if(iEndMoney == 0)
                sEndPriceCompare = "";
            else
                sEndPriceCompare = " AND SAN_PHAM.GIA <= " +iEndMoney.ToString();

            string sManufactureQuery = "";
            string sManufactureTable = "";
            if(sManufacture != "")
            {
                sManufactureTable = ", NHA_SAN_XUAT";
                sManufactureQuery = " AND SAN_PHAM.IDNSX = NHA_SAN_XUAT.ID AND NHA_SAN_XUAT.TENNSX = '" + sManufacture + "'";
            }

            string sSQLSelectProduct = "SELECT HINH_ANH.Url, SAN_PHAM.id,San_pham.Gia, SAN_PHAM.TenSanPham, SAN_PHAM.MoTa, SAN_PHAM.IdLoaiSanPham " +
                "FROM HINH_ANH, SAN_PHAM, LOAI_SAN_PHAM"+sManufactureTable+" WHERE HINH_ANH.idSanPham = SAN_PHAM.id AND SAN_PHAM.IDLOAISANPHAM = LOAI_SAN_PHAM.ID AND LOAI_SAN_PHAM.TENLOAISANPHAM = '" + sType +
                "'" + sManufactureQuery + sBeginPriceCompare + sEndPriceCompare;
            return m_DataProvider.ExecuteQueryDataTable(sSQLSelectProduct);
        }
    }
}
